package com.weixin.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.weixin.dao.IHouseGoodsDao;
import com.weixin.entity.HouseGoods;
import com.weixin.util.C3p0Util;

public class HouseGoodsDaoImpl implements IHouseGoodsDao {
	C3p0Util c3p0=new C3p0Util();
	
	@Override
	public List<HouseGoods> selectAll() throws SQLException {
		String sql="SELECT * from house_goods_tb where is_show=1";
		return c3p0.getqr().query(sql, new BeanListHandler<HouseGoods>(HouseGoods.class));
	}

	@Override
	public HouseGoods selectById(int h_goods_id) throws SQLException {
		String sql="SELECT * from house_goods_tb where h_goods_id=? and is_show=1";
		return c3p0.getqr().query(sql, new BeanHandler<HouseGoods>(HouseGoods.class),h_goods_id);
	}

	@Override
	public boolean insertHGoods(HouseGoods houseGoods) throws SQLException {
		String sql="insert into house_goods_tb(goods_id,number,sum,storehouse_id,remark) values(?,?,?,?,?)";
		int i=c3p0.getqr().update(sql,houseGoods.getGoods_id(),houseGoods.getNumber(),houseGoods.getSum(),houseGoods.getStorehouse_id(),houseGoods.getRemark());
		return i>0;
	}

	@Override
	public boolean updateHGoods(HouseGoods houseGoods) throws SQLException {
		String sql="update house_goods_tb set goods_id=?,number=?,sum=?,storehouse_id=?,remark=?,volume=? where h_goods_id=?";
		int i=c3p0.getqr().update(sql,houseGoods.getGoods_id(),houseGoods.getNumber(),houseGoods.getSum(),houseGoods.getStorehouse_id(),houseGoods.getRemark(),houseGoods.getVolume(),houseGoods.getH_goods_id());
		return i>0;
	}

	@Override
	public boolean deleteById(int h_goods_id) throws SQLException {
		String sql="update house_goods_tb set is_show=0 where h_goods_id=?";
		int i=c3p0.getqr().update(sql, h_goods_id);
		return i>0;
	}

	@Override
	public List<HouseGoods> getAllAbout(int pageSize,int pageNum) throws SQLException {
		String sql="select h.*,g.goods_name,g.unit,s.storehouse_name\r\n" + 
				"from house_goods_tb h,goods_tb g,storehouse_tb s\r\n" + 
				"where h.goods_id=g.goods_id and h.storehouse_id=s.storehouse_id and h.is_show=1 limit ?,?";
		return c3p0.getqr().query(sql,new BeanListHandler<HouseGoods>(HouseGoods.class),(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public HouseGoods selectByGoodsId(int goods_id) throws SQLException {
		String sql="select * from house_goods_tb where goods_id=? and is_show=1";
		return c3p0.getqr().query(sql, new BeanHandler<HouseGoods>(HouseGoods.class),goods_id);
	}

	@Override
	public int getAllAboutTotal() throws SQLException {
		String sql="select count(*) from house_goods_tb where is_show=1";
		
		return (int)((long)c3p0.getqr().query(sql, new ScalarHandler()));
	}

	@Override
	public List<HouseGoods> getSearchAbout(int pageSize, int pageNum,String search) throws SQLException {
		String sql="select h.*,g.goods_name,g.unit,s.storehouse_name\r\n" + 
				"from house_goods_tb h,goods_tb g,storehouse_tb s\r\n" + 
				"where h.goods_id=g.goods_id and h.storehouse_id=s.storehouse_id and h.is_show=1 and ( h.h_goods_id LIKE ? or h.number like ? or h.sum like ? or h.volume like ? or h.remark LIKE ? or g.goods_name like ? or g.unit like ? or s.storehouse_name like ?) limit ?,?";
		return c3p0.getqr().query(sql,new BeanListHandler<HouseGoods>(HouseGoods.class),"%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%",(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public int getSearchAboutTotal(String search) throws SQLException {
		String sql="select count(*)\r\n" + 
				"from house_goods_tb h,goods_tb g,storehouse_tb s\r\n" + 
				"where h.goods_id=g.goods_id and h.storehouse_id=s.storehouse_id and h.is_show=1 and ( h.h_goods_id LIKE ? or h.number like ? or h.sum like ? or h.volume like ? or h.remark LIKE ? or g.goods_name like ? or g.unit like ? or s.storehouse_name like ?)";
		return (int)((long)c3p0.getqr().query(sql,new ScalarHandler(),"%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%","%"+search+"%"));
	}

	@Override
	public HouseGoods getAllAboutById(int h_goods_id) throws SQLException {
		String sql="select h.*,g.goods_name,g.unit,s.storehouse_name\r\n" + 
				"from house_goods_tb h,goods_tb g,storehouse_tb s\r\n" + 
				"where h.goods_id=g.goods_id and h.storehouse_id=s.storehouse_id and h.is_show=1 and h.h_goods_id=?";
		return c3p0.getqr().query(sql, new BeanHandler<HouseGoods>(HouseGoods.class),h_goods_id);
	}

	@Override
	public boolean addPictureForGoos(String picture_name,int h_goods_id) throws SQLException {
		String sql="update house_goods_tb set picture=? where h_goods_id=?";	
		return c3p0.getqr().update(sql,picture_name,h_goods_id)>0;
		
	}

	@Override
	public List<HouseGoods> getAllForPicture() throws SQLException {
		String sql="SELECT h.*, g.goods_name FROM house_goods_tb h, goods_tb g WHERE h.is_show = 1 AND h.goods_id = g.goods_id";
		return c3p0.getqr().query(sql, new BeanListHandler<HouseGoods>(HouseGoods.class));
	}

	@Override
	public boolean addIntroduceForGoods(String introduce, int h_goods_id) throws SQLException {
		String sql="update house_goods_tb set introduce=? where h_goods_id=?";	
		return c3p0.getqr().update(sql,introduce,h_goods_id)>0;
	}

	@Override
	public List<HouseGoods> getAllAboutForExcel() throws SQLException {
		String sql="select h.*,g.goods_name,g.unit,s.storehouse_name\r\n" + 
				"from house_goods_tb h,goods_tb g,storehouse_tb s\r\n" + 
				"where h.goods_id=g.goods_id and h.storehouse_id=s.storehouse_id and h.is_show=1";
		return c3p0.getqr().query(sql,new BeanListHandler<HouseGoods>(HouseGoods.class));
	}
}
